<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width">
<meta name="theme-color" content="#222" media="(prefers-color-scheme: light)">
<meta name="theme-color" content="#222" media="(prefers-color-scheme: dark)"><meta name="generator" content="Hexo 6.3.0">
<link rel="preconnect" href="https://fonts.googleapis.com" crossorigin>
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Source+Sans+Pro:300,300italic,400,400italic,700,700italic%7CZCOOL+QingKe+HuangYou:300,300italic,400,400italic,700,700italic%7CNoto+Sans+Simplified+Chinese:300,300italic,400,400italic,700,700italic%7CJetBrains+Mono:300,300italic,400,400italic,700,700italic&display=swap&subset=latin,latin-ext">

<link rel="stylesheet" href="/lib/@fortawesome/fontawesome-free/css/all.min.css" integrity="sha256-HtsXJanqjKTc8vVQjO4YMhiqFoXkfBsjBWcX91T1jr8=" crossorigin="anonymous">
  <link rel="stylesheet" href="/lib/animate.css/animate.min.css" integrity="sha256-PR7ttpcvz8qrF57fur/yAx1qXMFJeJFiA6pSzWi0OIE=" crossorigin="anonymous">
  <link rel="stylesheet" href="/lib/@fancyapps/ui/dist/fancybox/fancybox.css" integrity="sha256-RvRHGSuWAxZpXKV9lLDt2e+rZ+btzn48Wp4ueS3NZKs=" crossorigin="anonymous">

<script class="next-config" data-name="main" type="application/json">{"hostname":"wyatex.online","root":"/","images":"/images","scheme":"Pisces","darkmode":true,"version":"8.18.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12},"copycode":{"enable":true,"style":"mac"},"fold":{"enable":false,"height":500},"bookmark":{"enable":false,"color":"#222","save":"auto"},"mediumzoom":false,"lazyload":true,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"stickytabs":false,"motion":{"enable":true,"async":true,"transition":{"menu_item":"fadeInDown","post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"prism":false,"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"},"path":"/search.xml","localsearch":{"enable":true,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":true}}</script><script src="/js/config.js"></script>

    <meta name="description" content="存储过程的英文是 Stored Procedure。它的思想很简单，就是 SQL 语句的封装。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。存储过程实际上由 SQL 语句和流控制语句共同组成。">
<meta property="og:type" content="article">
<meta property="og:title" content="存储过程笔记">
<meta property="og:url" content="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E7%AC%94%E8%AE%B0/index.html">
<meta property="og:site_name" content="Wyatex&#96;s blog">
<meta property="og:description" content="存储过程的英文是 Stored Procedure。它的思想很简单，就是 SQL 语句的封装。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。存储过程实际上由 SQL 语句和流控制语句共同组成。">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="2020-05-20T13:17:32.000Z">
<meta property="article:modified_time" content="2023-05-05T01:25:47.526Z">
<meta property="article:author" content="Wyatex">
<meta property="article:tag" content="学习笔记">
<meta property="article:tag" content="SQL">
<meta name="twitter:card" content="summary">


<link rel="canonical" href="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E7%AC%94%E8%AE%B0/">



<script class="next-config" data-name="page" type="application/json">{"sidebar":"","isHome":false,"isPost":true,"lang":"zh-CN","comments":true,"permalink":"https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E7%AC%94%E8%AE%B0/","path":"学习笔记/存储过程笔记/","title":"存储过程笔记"}</script>

<script class="next-config" data-name="calendar" type="application/json">""</script>
<title>存储过程笔记 | Wyatex`s blog</title>
  








  <noscript>
    <link rel="stylesheet" href="/css/noscript.css">
  </noscript>
</head>

<body itemscope itemtype="http://schema.org/WebPage" class="use-motion">
  <div class="headband"></div>

  <main class="main">
    <div class="column">
      <header class="header" itemscope itemtype="http://schema.org/WPHeader"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏" role="button">
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <i class="logo-line"></i>
      <p class="site-title">Wyatex`s blog</p>
      <i class="logo-line"></i>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger" aria-label="搜索" role="button">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>



<nav class="site-nav">
  <ul class="main-menu menu"><li class="menu-item menu-item-home"><a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a></li><li class="menu-item menu-item-about"><a href="/about/" rel="section"><i class="fa fa-user fa-fw"></i>关于</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签<span class="badge">97</span></a></li><li class="menu-item menu-item-categories"><a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类<span class="badge">26</span></a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档<span class="badge">155</span></a></li><li class="menu-item menu-item-编程"><a href="/programming/" rel="section"><i class="fa fa-book fa-fw"></i>编程</a></li><li class="menu-item menu-item-蹲坑读物"><a href="/%E5%B9%B2%E8%B4%A7/%E5%BC%80%E5%8F%91%E4%BA%A4%E6%B5%81%E7%BE%A4%E5%88%86%E4%BA%AB%E8%AE%B0%E5%BD%95/" rel="section"><i class="fa fa-book fa-fw"></i>蹲坑读物</a></li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup"><div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off" maxlength="80"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close" role="button">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div class="search-result-container no-result">
  <div class="search-result-icon">
    <i class="fa fa-spinner fa-pulse fa-5x"></i>
  </div>
</div>

    </div>
  </div>

</header>
        
  
  <aside class="sidebar">

    <div class="sidebar-inner sidebar-nav-active sidebar-toc-active">
      <ul class="sidebar-nav">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <div class="sidebar-panel-container">
        <!--noindex-->
        <div class="post-toc-wrap sidebar-panel">
            <div class="post-toc animated"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#%E4%BB%80%E4%B9%88%E6%98%AF%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%EF%BC%8C%E5%A6%82%E4%BD%95%E5%88%9B%E5%BB%BA%E4%B8%80%E4%B8%AA%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B"><span class="nav-number">1.</span> <span class="nav-text">什么是存储过程，如何创建一个存储过程</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8DELIMITER-%E5%AE%9A%E4%B9%89%E8%AF%AD%E5%8F%A5%E7%9A%84%E7%BB%93%E6%9D%9F%E7%AC%A6"><span class="nav-number">2.</span> <span class="nav-text">使用DELIMITER 定义语句的结束符</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E5%8F%82%E6%95%B0%E5%88%97%E8%A1%A8"><span class="nav-number">3.</span> <span class="nav-text">参数列表</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E6%B5%81%E6%8E%A7%E5%88%B6%E8%AF%AD%E5%8F%A5"><span class="nav-number">4.</span> <span class="nav-text">流控制语句</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E5%85%B3%E4%BA%8E%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E4%BD%BF%E7%94%A8%E7%9A%84%E4%BA%89%E8%AE%AE"><span class="nav-number">5.</span> <span class="nav-text">关于存储过程使用的争议</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E5%B0%8F%E6%B5%8B%E8%AF%95"><span class="nav-number">6.</span> <span class="nav-text">小测试</span></a></li></ol></div>
        </div>
        <!--/noindex-->

        <div class="site-overview-wrap sidebar-panel">
          <div class="site-author animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="Wyatex"
      src="/images/avatar.jpg">
  <p class="site-author-name" itemprop="name">Wyatex</p>
  <div class="site-description" itemprop="description">这里是我用来做笔记的网站哟~</div>
</div>
<div class="site-state-wrap animated">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
        <a href="/archives/">
          <span class="site-state-item-count">155</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
          <a href="/categories/">
        <span class="site-state-item-count">26</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
          <a href="/tags/">
        <span class="site-state-item-count">97</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>
  <div class="links-of-author animated">
      <span class="links-of-author-item">
        <a href="https://github.com/wyatex" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;wyatex" rel="noopener me" target="_blank"><i class="fab fa-github fa-fw"></i>GitHub</a>
      </span>
      <span class="links-of-author-item">
        <a href="mailto:wyatex@qq.com" title="E-Mail → mailto:wyatex@qq.com" rel="noopener me" target="_blank"><i class="fa fa-envelope fa-fw"></i>E-Mail</a>
      </span>
  </div>

        </div>
      </div>
    </div>

    
    <div class="sidebar-inner sidebar-blogroll">
      <div class="links-of-blogroll animated">
        <div class="links-of-blogroll-title"><i class="fa fa-globe fa-fw"></i>
          链接
        </div>
        <ul class="links-of-blogroll-list">
            <li class="links-of-blogroll-item">
              <a href="https://antfu.me/" title="https:&#x2F;&#x2F;antfu.me&#x2F;" rel="noopener" target="_blank">Anthony Fu</a>
            </li>
            <li class="links-of-blogroll-item">
              <a href="https://www.codesky.me/" title="https:&#x2F;&#x2F;www.codesky.me&#x2F;" rel="noopener" target="_blank">CodeSky</a>
            </li>
            <li class="links-of-blogroll-item">
              <a href="https://learn.lianglianglee.com/" title="https:&#x2F;&#x2F;learn.lianglianglee.com&#x2F;" rel="noopener" target="_blank">技术文章摘抄</a>
            </li>
        </ul>
      </div>
    </div>
        <div class="pjax">
        <div class="sidebar-inner sidebar-post-related">
          <div class="animated">
              <div class="links-of-blogroll-title"><i class="fa fa-signs-post fa-fw"></i>
    相关文章
  </div>
  <ul class="popular-posts">
    <li class="popular-posts-item">
      <a class="popular-posts-link" href="/uncategorized/JsonSchema%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" rel="bookmark">
        <time class="popular-posts-time">2021-10-19</time>
        <br>
      JsonSchema学习笔记
      </a>
    </li>
    <li class="popular-posts-item">
      <a class="popular-posts-link" href="/%E7%AC%94%E8%AE%B0/%E4%BD%BF%E7%94%A8docker%E9%83%A8%E7%BD%B2mysql/" rel="bookmark">
        <time class="popular-posts-time">2020-05-06</time>
        <br>
      使用docker部署mysql
      </a>
    </li>
    <li class="popular-posts-item">
      <a class="popular-posts-link" href="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/" rel="bookmark">
        <time class="popular-posts-time">2020-11-20</time>
        <br>
      SQL 语法速成手册
      </a>
    </li>
    <li class="popular-posts-item">
      <a class="popular-posts-link" href="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E4%BA%8B%E5%8A%A1%E5%A4%84%E7%90%86/" rel="bookmark">
        <time class="popular-posts-time">2020-05-21</time>
        <br>
      什么是事务处理，如何使用COMMIT和ROLLBACK进行操作？
      </a>
    </li>
  </ul>

          </div>
        </div>
        </div>
  </aside>


    </div>

    <div class="main-inner post posts-expand">


  


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E7%AC%94%E8%AE%B0/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.jpg">
      <meta itemprop="name" content="Wyatex">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Wyatex`s blog">
      <meta itemprop="description" content="这里是我用来做笔记的网站哟~">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="存储过程笔记 | Wyatex`s blog">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          存储过程笔记
        </h1>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2020-05-20 21:17:32" itemprop="dateCreated datePublished" datetime="2020-05-20T21:17:32+08:00">2020-05-20</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2023-05-05 09:25:47" itemprop="dateModified" datetime="2023-05-05T09:25:47+08:00">2023-05-05</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" itemprop="url" rel="index"><span itemprop="name">学习笔记</span></a>
        </span>
    </span>

  
    <span class="post-meta-item" title="阅读次数" id="busuanzi_container_page_pv">
      <span class="post-meta-item-icon">
        <i class="far fa-eye"></i>
      </span>
      <span class="post-meta-item-text">阅读次数：</span>
      <span id="busuanzi_value_page_pv"></span>
    </span>
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody"><blockquote>
<p>存储过程的英文是 Stored Procedure。它的思想很简单，就是 SQL 语句的封装。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。存储过程实际上由 SQL 语句和流控制语句共同组成。</p>
</blockquote>
<span id="more"></span>

<h1 id="什么是存储过程，如何创建一个存储过程"><a href="#什么是存储过程，如何创建一个存储过程" class="headerlink" title="什么是存储过程，如何创建一个存储过程"></a>什么是存储过程，如何创建一个存储过程</h1><p>存储过程定义：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> 存储过程名称 ([参数列表])</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">    需要执行的语句</span><br><span class="line"><span class="keyword">END</span>    </span><br></pre></td></tr></table></figure>
<p>在这里，我们使用 CREATE PROCEDURE 创建一个存储过程，后面是存储过程的名称，以及过程所带的参数，可以包括输入参数和输出参数。最后由 BEGIN 和 END 来定义我们所要执行的语句块。</p>
<p>和视图一样，我们可以删除已经创建的存储过程，使用的是 DROP PROCEDURE。如果要更新存储过程，我们需要使用 ALTER PROCEDURE。</p>
<p>实现一个从1到n的求和：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> `add_num`(<span class="keyword">IN</span> n <span class="type">INT</span>)</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">       <span class="keyword">DECLARE</span> i <span class="type">INT</span>;</span><br><span class="line">       <span class="keyword">DECLARE</span> sum <span class="type">INT</span>;</span><br><span class="line">       </span><br><span class="line">       <span class="keyword">SET</span> i <span class="operator">=</span> <span class="number">1</span>;</span><br><span class="line">       <span class="keyword">SET</span> sum <span class="operator">=</span> <span class="number">0</span>;</span><br><span class="line">       WHILE i <span class="operator">&lt;=</span> n DO</span><br><span class="line">              <span class="keyword">SET</span> sum <span class="operator">=</span> sum <span class="operator">+</span> i;</span><br><span class="line">              <span class="keyword">SET</span> i <span class="operator">=</span> i <span class="operator">+</span><span class="number">1</span>;</span><br><span class="line">       <span class="keyword">END</span> WHILE;</span><br><span class="line">       <span class="keyword">SELECT</span> sum;</span><br><span class="line"><span class="keyword">END</span></span><br></pre></td></tr></table></figure>
<p>当使用时直接使用<code>CALL add_num(n)</code>,比如传入50，就可以实现1+2+…+50。</p>
<table>
<thead>
<tr>
<th>sum</th>
</tr>
</thead>
<tbody><tr>
<td>1275</td>
</tr>
</tbody></table>
<h1 id="使用DELIMITER-定义语句的结束符"><a href="#使用DELIMITER-定义语句的结束符" class="headerlink" title="使用DELIMITER 定义语句的结束符"></a>使用DELIMITER 定义语句的结束符</h1><p>我们先来看下 DELIMITER 的作用。如果你使用 Navicat 这个工具来管理 MySQL 执行存储过程，那么直接执行上面这段代码就可以了。如果用的是 MySQL，你还需要用 DELIMITER 来临时定义新的结束符。因为默认情况下 SQL 采用（；）作为结束符，这样当存储过程中的每一句 SQL 结束之后，采用（；）作为结束符，就相当于告诉 SQL 可以执行这一句了。但是存储过程是一个整体，我们不希望 SQL 逐条执行，而是采用存储过程整段执行的方式，因此我们就需要临时定义新的 DELIMITER，新的结束符可以用（&#x2F;&#x2F;）或者（$$）。如果你用的是 MySQL，那么上面这段代码，应该写成下面这样：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">DELIMITER <span class="operator">/</span><span class="operator">/</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> `add_num`(<span class="keyword">IN</span> n <span class="type">INT</span>)</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">       <span class="keyword">DECLARE</span> i <span class="type">INT</span>;</span><br><span class="line">       <span class="keyword">DECLARE</span> sum <span class="type">INT</span>;</span><br><span class="line">       </span><br><span class="line">       <span class="keyword">SET</span> i <span class="operator">=</span> <span class="number">1</span>;</span><br><span class="line">       <span class="keyword">SET</span> sum <span class="operator">=</span> <span class="number">0</span>;</span><br><span class="line">       WHILE i <span class="operator">&lt;=</span> n DO</span><br><span class="line">              <span class="keyword">SET</span> sum <span class="operator">=</span> sum <span class="operator">+</span> i;</span><br><span class="line">              <span class="keyword">SET</span> i <span class="operator">=</span> i <span class="operator">+</span><span class="number">1</span>;</span><br><span class="line">       <span class="keyword">END</span> WHILE;</span><br><span class="line">       <span class="keyword">SELECT</span> sum;</span><br><span class="line"><span class="keyword">END</span> <span class="operator">/</span><span class="operator">/</span></span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure>

<p>PS：使用Navicat工具时，在编写存储过程的时候它会自动使用DELIMITER将结束符设置成其他符号，就不需要我们手写。</p>
<h1 id="参数列表"><a href="#参数列表" class="headerlink" title="参数列表"></a>参数列表</h1><p>我们再来看下存储过程的 3 种参数类型。在刚才的存储过程中，我们使用了 IN 类型的参数，另外还有 OUT 类型和 INOUT 类型，作用如下：</p>
<table>
<thead>
<tr>
<th>参数类型</th>
<th>是否返回</th>
<th>作用</th>
</tr>
</thead>
<tbody><tr>
<td>IN</td>
<td>否</td>
<td>向存储过程传入参数，存储过程中修改该参数的值，不能被返回</td>
</tr>
<tr>
<td>OUT</td>
<td>是</td>
<td>把存储过程计算的结构放入该参数，调用者可以得到返回值</td>
</tr>
<tr>
<td>INOUT</td>
<td>是</td>
<td>IN和OUT的结合，先传入然后将结构放入该参数，可以得到返回值</td>
</tr>
</tbody></table>
<p>拿<a target="_blank" rel="noopener" href="https://github.com/cystanford/sql_heros_data">heros表</a>举例，假设我想创建一个存储类型 get_hero_scores，用来查询某一类型英雄中的最大的最大生命值，最小的最大魔法值，以及平均最大攻击值，那么该怎么写呢？</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> `get_hero_scores`(</span><br><span class="line">    <span class="keyword">OUT</span> max_max_hp <span class="type">FLOAT</span>,</span><br><span class="line">    <span class="keyword">OUT</span> min_max_mp <span class="type">FLOAT</span>,</span><br><span class="line">    <span class="keyword">OUT</span> avg_max_attack <span class="type">FLOAT</span>,  </span><br><span class="line">    s <span class="type">VARCHAR</span>(<span class="number">255</span>)</span><br><span class="line">    )</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">    <span class="keyword">SELECT</span> <span class="built_in">MAX</span>(hp_max), <span class="built_in">MIN</span>(mp_max), <span class="built_in">AVG</span>(attack_max) <span class="keyword">FROM</span> heros <span class="keyword">WHERE</span> role_main <span class="operator">=</span> s <span class="keyword">INTO</span> max_max_hp, min_max_mp, avg_max_attack;</span><br><span class="line"><span class="keyword">END</span></span><br></pre></td></tr></table></figure>
<p>能看到我定义了 4 个参数类型，其中 3 个为 OUT 类型，分别为 max_max_hp、min_max_mp 和 avg_max_attack，另一个参数 s 为 IN 类型。（这里IN省略了估计是缺省值是IN）</p>
<p>这里我们从 heros 数据表中筛选主要英雄定位为 s 的英雄数据，即筛选条件为 role_main&#x3D;s，提取这些数据中的最大的最大生命值，最小的最大魔法值，以及平均最大攻击值，分别赋值给变量 max_max_hp、min_max_mp 和 avg_max_attack。</p>
<p>接下来我们调用存储过程：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CALL</span> get_hero_scores(<span class="variable">@max_max_hp</span>, <span class="variable">@min_max_mp</span>, <span class="variable">@avg_max_attack</span>, <span class="string">&#x27;战士&#x27;</span>);</span><br><span class="line"><span class="keyword">SELECT</span> <span class="variable">@max_max_hp</span>, <span class="variable">@min_max_mp</span>, <span class="variable">@avg_max_attack</span>;</span><br></pre></td></tr></table></figure>
<p>（单个@加变量名在 MySQL 代表自定义变量，这里相当于自定义个变量，然后接收函数返回的结果）</p>
<p>得到下面结果：</p>
<table>
<thead>
<tr>
<th>@max_max_hp</th>
<th>@min_max_mp</th>
<th>@avg_max_attack</th>
</tr>
</thead>
<tbody><tr>
<td>8050</td>
<td>0</td>
<td>342.1666564941406</td>
</tr>
</tbody></table>
<h1 id="流控制语句"><a href="#流控制语句" class="headerlink" title="流控制语句"></a>流控制语句</h1><p>流控制语句是用来做流程控制的，我刚才讲了两个简单的存储过程的例子，一个是 1+2+…+n 的结果计算，一个是王者荣耀的数据查询，你能看到这两个例子中，我用到了下面的流控制语句：</p>
<ol>
<li>BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。</li>
<li>DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。</li>
<li>SET：赋值语句，用于对变量进行赋值。</li>
<li>SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。</li>
</ol>
<p>除了上面这些用到的流控制语句以外，还有一些常用的流控制语句：</p>
<ol>
<li>IF…THEN…ENDIF：条件判断语句，我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。</li>
<li>CASE：CASE 语句用于多条件的分支判断，使用的语法是下面这样的。<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CASE</span> </span><br><span class="line">	<span class="keyword">WHEN</span> expression1 <span class="keyword">THEN</span> ...</span><br><span class="line">	<span class="keyword">WHEN</span> expression2 <span class="keyword">THEN</span> ...</span><br><span class="line">	...</span><br><span class="line">    <span class="keyword">ELSE</span> </span><br><span class="line">    <span class="comment">--ELSE 语句可以加，也可以不加。加的话代表的所有条件都不满足时采用的方式。</span></span><br><span class="line"><span class="keyword">END</span></span><br></pre></td></tr></table></figure></li>
<li>LOOP、LEAVE 和 ITERATE：LOOP 是循环语句，使用 LEAVE 可以跳出循环，使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 BREAK，把 ITERATE 理解为 CONTINUE。</li>
<li>REPEAT…UNTIL…END REPEAT：这是一个循环语句，首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。</li>
<li>WHILE…DO…END WHILE：这也是循环语句，和 REPEAT 循环不同的是，这个语句需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。</li>
</ol>
<h1 id="关于存储过程使用的争议"><a href="#关于存储过程使用的争议" class="headerlink" title="关于存储过程使用的争议"></a>关于存储过程使用的争议</h1><p>尽管存储过程有诸多优点，但是对于存储过程的使用，一直都存在着很多争议，比如有些公司对于大型项目要求使用存储过程，而有些公司在手册中明确禁止使用存储过程，为什么这些公司对存储过程的使用需求差别这么大呢？</p>
<p>我们得从存储过程的特点来找答案。</p>
<p>你能看到存储过程有很多好处。</p>
<p>首先存储过程可以一次编译多次使用。存储过程只在创造时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。其次它可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。还有一点，存储过程的安全性强，我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。最后它可以减少网络传输量，因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。同时在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。</p>
<p>基于上面这些优点，不少大公司都要求大型项目使用存储过程，比如微软、IBM 等公司。但是国内的阿里并不推荐开发人员使用存储过程，这是为什么呢？</p>
<p>存储过程虽然有诸如上面的好处，但缺点也是很明显的。</p>
<p>它的可移植性差，存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。</p>
<p>其次调试困难，只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。</p>
<p>此外，存储过程的版本管理也很困难，比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。</p>
<p>最后它不适合高并发的场景，高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。</p>
<p>了解了存储过程的优缺点之后，我想说的是，存储过程既方便，又有局限性。尽管不同的公司对存储过程的态度不一，但是对于我们开发人员来说，不论怎样，掌握存储过程都是必备的技能之一。</p>
<h1 id="小测试"><a href="#小测试" class="headerlink" title="小测试"></a>小测试</h1><blockquote>
<p>针对王者荣耀的英雄数据表 heros 表，请编写存储过程 get_sum_score，用来得到某一类型英雄（主要定位为某一类型即可）的最大生命值的总和。</p>
</blockquote>
<p>这里是我的答案：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> `get_sum_scores`(<span class="keyword">IN</span> mainRule <span class="type">VARCHAR</span>(<span class="number">255</span>))</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">	<span class="keyword">SELECT</span> <span class="built_in">SUM</span>(hp_max) <span class="keyword">AS</span> sum_hp_max <span class="keyword">FROM</span> heros <span class="keyword">WHERE</span> role_main <span class="operator">=</span> role_main;</span><br><span class="line"><span class="keyword">END</span></span><br></pre></td></tr></table></figure>
<p>和有返回值，而且可以在MySQl用的：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">DELIMITER <span class="operator">/</span><span class="operator">/</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> `get_sum_scores`(<span class="keyword">IN</span> mainRule <span class="type">VARCHAR</span>(<span class="number">255</span>), <span class="keyword">OUT</span> hp_max_sum <span class="type">FLOAT</span>)</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">	<span class="keyword">SELECT</span> <span class="built_in">SUM</span>(hp_max) <span class="keyword">AS</span> sum_hp_max <span class="keyword">FROM</span> heros <span class="keyword">WHERE</span> role_main <span class="operator">=</span> role_main <span class="keyword">INTO</span> hp_max_sum;</span><br><span class="line"><span class="keyword">END</span> <span class="operator">/</span><span class="operator">/</span></span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure>
<p>对应的执行语句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CALL</span> get_sum_scores1(<span class="string">&#x27;战士&#x27;</span>, <span class="variable">@sum</span>);</span><br><span class="line"><span class="keyword">SELECT</span> <span class="variable">@sum</span> <span class="keyword">AS</span> sum_hp_max;</span><br></pre></td></tr></table></figure>
    </div>

    
    
    

    <footer class="post-footer">
          

<div class="post-copyright">
<ul>
  <li class="post-copyright-author">
      <strong>本文作者： </strong>Wyatex
  </li>
  <li class="post-copyright-link">
      <strong>本文链接：</strong>
      <a href="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E7%AC%94%E8%AE%B0/" title="存储过程笔记">https://wyatex.online/学习笔记/存储过程笔记/</a>
  </li>
  <li class="post-copyright-license">
      <strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" rel="noopener" target="_blank"><i class="fab fa-fw fa-creative-commons"></i>BY-NC-SA</a> 许可协议。转载请注明出处！
  </li>
</ul>
</div>

          <div class="post-tags">
              <a href="/tags/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" rel="tag"># 学习笔记</a>
              <a href="/tags/SQL/" rel="tag"># SQL</a>
          </div>

        

          <div class="post-nav">
            <div class="post-nav-item">
                <a href="/%E7%AC%94%E8%AE%B0/%E9%83%A8%E7%BD%B2docker%E9%95%9C%E5%83%8F%E6%9C%8D%E5%8A%A1%E5%99%A8/" rel="prev" title="部署docker镜像服务器">
                  <i class="fa fa-angle-left"></i> 部署docker镜像服务器
                </a>
            </div>
            <div class="post-nav-item">
                <a href="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E4%BA%8B%E5%8A%A1%E5%A4%84%E7%90%86/" rel="next" title="什么是事务处理，如何使用COMMIT和ROLLBACK进行操作？">
                  什么是事务处理，如何使用COMMIT和ROLLBACK进行操作？ <i class="fa fa-angle-right"></i>
                </a>
            </div>
          </div>
    </footer>
  </article>
</div>






</div>
  </main>

  <footer class="footer">
    <div class="footer-inner">

  <div class="copyright">
    &copy; 2019 – 
    <span itemprop="copyrightYear">2024</span>
    <span class="with-love">
      <i class="fa fa-heart"></i>
    </span>
    <span class="author" itemprop="copyrightHolder">Wyatex</span>
  </div>
<div class="busuanzi-count">
    <span class="post-meta-item" id="busuanzi_container_site_uv">
      <span class="post-meta-item-icon">
        <i class="fa fa-user"></i>
      </span>
      <span class="site-uv" title="总访客量">
        <span id="busuanzi_value_site_uv"></span>
      </span>
    </span>
    <span class="post-meta-item" id="busuanzi_container_site_pv">
      <span class="post-meta-item-icon">
        <i class="fa fa-eye"></i>
      </span>
      <span class="site-pv" title="总访问量">
        <span id="busuanzi_value_site_pv"></span>
      </span>
    </span>
</div>

    </div>
  </footer>

  
  <div class="back-to-top" role="button" aria-label="返回顶部">
    <i class="fa fa-arrow-up fa-lg"></i>
    <span>0%</span>
  </div>
  <div class="reading-progress-bar"></div>

<noscript>
  <div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>


  
  <script size="300" alpha="0.6" zIndex="-1" src="/lib/ribbon.js/dist/ribbon.min.js"></script>
  <script src="/lib/animejs/lib/anime.min.js" integrity="sha256-XL2inqUJaslATFnHdJOi9GfQ60on8Wx1C2H8DYiN1xY=" crossorigin="anonymous"></script>
  <script src="/lib/@next-theme/pjax/pjax.min.js" integrity="sha256-vxLn1tSKWD4dqbMRyv940UYw4sXgMtYcK6reefzZrao=" crossorigin="anonymous"></script>
  <script src="/lib/@fancyapps/ui/dist/fancybox/fancybox.umd.js" integrity="sha256-q8XkJ6dj5VwSvzI8+nATCHHQG+Xv/dAZBCgqmu93zOY=" crossorigin="anonymous"></script>
  <script src="/lib/lozad/dist/lozad.min.js" integrity="sha256-mOFREFhqmHeQbXpK2lp4nA3qooVgACfh88fpJftLBbc=" crossorigin="anonymous"></script>
<script src="/js/comments.js"></script><script src="/js/utils.js"></script><script src="/js/motion.js"></script><script src="/js/next-boot.js"></script><script src="/js/pjax.js"></script>

  <script src="/lib/hexo-generator-searchdb/dist/search.js" integrity="sha256-1kfA5uHPf65M5cphT2dvymhkuyHPQp5A53EGZOnOLmc=" crossorigin="anonymous"></script>
<script src="/js/third-party/search/local-search.js"></script>




  <script src="/js/third-party/fancybox.js"></script>



  
  <script data-pjax async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>





</body>
</html>
